﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using LogLibrary;

namespace SQLAccessManager
{
    public class QueryManager : IDisposable
    {
        private SqlConnection connection;
        private SqlCommand command;


        private void SetUpParameters(List<SqlParameter> paramterList)
        {
            foreach (SqlParameter param in paramterList)
            {
                command.Parameters.Add(param);
            }
        }

        private void Execute(string connectionString, string commandString, List<SqlParameter> queryParameters, System.Data.CommandType type)
        {
            try
            {
                connection = new SqlConnection(connectionString);
                command = new SqlCommand(commandString);
                command.Connection = connection;
                command.CommandType = type;
                SetUpParameters(queryParameters);

                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                Dispose();
            }
        }

        private SqlDataReader ExecuteReader(string connectionString, string commandString, List<SqlParameter> queryParameters, System.Data.CommandType type)
        {
            try
            {
                connection = new SqlConnection(connectionString);
                command = new SqlCommand(commandString);
                command.Connection = connection;
                command.CommandType = type;
                SetUpParameters(queryParameters);

                command.Connection.Open();
                return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            catch (Exception ea)
            {
                EventLogger.Log.LogError(ea);
                return null;
            }
        }

        private void ExecuteNonQuery(string connectionString, string commandString, List<SqlParameter> queryParameters)
        {
            Execute(connectionString, commandString, queryParameters, System.Data.CommandType.Text);
        }

        private void ExecuteQueryReader(string connectionString, string commandString, List<SqlParameter> queryParameters, SQLAccessManager.Interfaces.IPopulateList listPopulate)
        {
		  listPopulate.PopulateList(ExecuteReader(connectionString, commandString, queryParameters, System.Data.CommandType.Text));
        }

        private void ExecuteNonQueryStoredProc(string connectionString, string commandString, List<SqlParameter> queryParameters)
        {
            Execute(connectionString, commandString, queryParameters, System.Data.CommandType.StoredProcedure);
        }

	    private void ExecuteStoredProcReader(string connectionString, string commandString, List<SqlParameter> queryParameters, SQLAccessManager.Interfaces.IPopulateList listPopulat)
        {
            listPopulat.PopulateList(ExecuteReader(connectionString, commandString, queryParameters, System.Data.CommandType.StoredProcedure));
        }

        /// <summary>
        /// 
        /// </summary>
        public void ExecuteQueryString(string connectionString, string commandString)
        {
            ExecuteQueryString(connectionString, commandString, queryParameters: null);
        }

        /// <summary>
        /// 
        /// </summary>
        public void ExecuteQueryString(string connectionString, string commandString, List<SqlParameter> queryParameters)
        {
            ExecuteNonQuery(connectionString, commandString, queryParameters);
        }

        /// <summary>
        /// 
        /// </summary>
        public void ExecuteStoredProc(string connectionString, string commandString, List<SqlParameter> queryParameters)
        {
            ExecuteNonQueryStoredProc(connectionString, commandString, queryParameters);
        }

        public void ExecuteStoredProc(string connectionString, string commandString, List<SqlParameter> queryParameters, SQLAccessManager.Interfaces.IPopulateList listPopulat)
        {
            ExecuteStoredProcReader(connectionString, commandString, queryParameters, listPopulat);
        }

        /// <summary>
        /// 
        /// </summary>
        public void ExecuteStoredProc(string connectionString, string commandString)
        {
            ExecuteNonQueryStoredProc(connectionString, commandString, queryParameters: null);
        }

        /// <summary>
        /// Disposing Resources
        /// 
        /// Making use of the Disposable Pattern allows the use of the using keyword when declaring
        /// an instance of the QueryManager
        /// e.g. using(QueryManager manager = new QueryManager())
        ///      {
        ///         // code
        ///      }
        /// </summary>
        public void Dispose()
        {
            if ((connection != null) && 
                (connection.State != System.Data.ConnectionState.Closed))
            {
                connection.Close();
            }
        }
    }
}
